package com.c2soft.nepalcmx.dao;

import java.util.Date;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.c2soft.nepalcmx.command.ConfirmTradeCommand;
import com.c2soft.nepalcmx.command.TradeBookCommand;
import com.c2soft.nepalcmx.command.TradeBookCommand;


public class TradeBookDAO {

	@Autowired
	private DataSource dataSource;

	private JdbcTemplate jdbcTemplate;
	
	

	public List<TradeBookCommand> findAllTradeBookEntries(String userId) {
		String sql = "select * from ordermgmt where user_id= ?";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<TradeBookCommand> tradeBookEntries=jdbcTemplate.query(sql,new Object[] {userId},new BeanPropertyRowMapper(TradeBookCommand.class));
		return tradeBookEntries;

	}
	
	public List<TradeBookCommand> findAllTradeBooksEntries(String brokeId) {
		String sql = "select * from ordermgmt where brokeId= ? and user_id='' and status='Executed'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<TradeBookCommand> tradeBookEntries=jdbcTemplate.query(sql,new Object[] {brokeId},new BeanPropertyRowMapper(TradeBookCommand.class));
		return tradeBookEntries;

	}
	
	public List<TradeBookCommand> findAllTradeBooksEntries1() {
		String sql = "select * from ordermgmt where brokeId='' and user_Id='' and status='Executed'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<TradeBookCommand> tradeBookEntries=jdbcTemplate.query(sql,new BeanPropertyRowMapper(TradeBookCommand.class));
		return tradeBookEntries;

	}

	public List<TradeBookCommand> findPrevTradeBookEntries(String userId) {
		String sql = "select * from ordermgmt where user_id= ? and status='Executed' and DATE(ts) != CURDATE()";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<TradeBookCommand> tradeBookEntries=jdbcTemplate.query(sql,new Object[] {userId},new BeanPropertyRowMapper(TradeBookCommand.class));
		return tradeBookEntries;

	}
	
	public List<TradeBookCommand> findPrevTradeBooksEntries(String brokeId) {
		String sql = "select * from ordermgmt where brokeId= ? and user_id='' and status='Executed' and DATE(ts) != CURDATE()";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<TradeBookCommand> tradeBookEntries=jdbcTemplate.query(sql,new Object[] {brokeId},new BeanPropertyRowMapper(TradeBookCommand.class));
		return tradeBookEntries;

	}
	
	public List<TradeBookCommand> findPrevTradeBooksEntries1() {
		String sql = "select * from ordermgmt where  brokeId='' and user_Id='' and status='Executed' and DATE(ts) != CURDATE()";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<TradeBookCommand> tradeBookEntries=jdbcTemplate.query(sql,new BeanPropertyRowMapper(TradeBookCommand.class));
		return tradeBookEntries;

	}

	public List<TradeBookCommand> findAllTradeBooks(String userId,ConfirmTradeCommand confirmTrade) throws ParseException {
		String fromDate=confirmTrade.getFromDate();
		
		String toDate=confirmTrade.getToDate();
		String symbol=confirmTrade.getSymbol();
		
		String qty=confirmTrade.getQty();
		
		
		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		Date d1=(Date) formatter.parse(fromDate);		
		
		Date d2=(Date) formatter.parse(toDate);
		
		String sql="";
		if(symbol==null || symbol.equals("")){
			
			sql= "select * from ordermgmt where user_Id=? and DATE(ts) BETWEEN str_to_date('"+formatter.format(d1)+"', '%d/%m/%Y') AND str_to_date('"+formatter.format(d2)+"', '%d/%m/%Y')";
			
		}
		else{
			sql= "select * from ordermgmt where user_Id=? and DATE(ts) BETWEEN str_to_date('"+formatter.format(d1)+"', '%d/%m/%Y') AND str_to_date('"+formatter.format(d2)+"', '%d/%m/%Y') AND SYMBOL='"+symbol+"'";
				
		}
		
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<TradeBookCommand> confirmtradeBookEntries=jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper(TradeBookCommand.class));
		return confirmtradeBookEntries;

	}

}
